/**
 *	主要功能：
 *      1.创建过程
 *  作者：liuzhh
 */

CREATE OR REPLACE PROCEDURE PROD_SYSN_PERSON
(
       varAreaCode in varchar2
)
IS
     v_sql varchar2(1000); /* sql */
     seq_sql  varchar2(1000); /* 序列sql */
     uum_sync_code  varchar2(100); /* 用户同步码 */
     mis_key  varchar2(100); /* mis_key */
     curr_person_name varchar2(60);/* 人员信息 */
     curr_user_id varchar2(60);/* 用户信息 */
     temp_person_id varchar2(60);/* 人员id */
     temp_user_id varchar2(60);/* 用户id */
     temp_roleuser_id varchar2(60);/* 用户关联角色id */

     /* 用户游标 */
     CURSOR c_person(CORP_AREA VARCHAR2) IS
          SELECT
              DISTINCT s.userid,s.username,s.uum_sync_code,s.userid_s
          FROM prj_suser s,prj_suserdept t
          WHERE
              s.userid IS NOT NULL
              AND
              s.username IS NOT NULL
              AND
              s.userid=t.userid(+)
              AND
              t.areacode=CORP_AREA
              AND
              NOT EXISTS
              (
                  SELECT a.user_id
                  FROM T_SECU_USER a
                  WHERE a.user_id=s.userid
               );

BEGIN
     OPEN c_person(varAreaCode);
     LOOP
          FETCH c_person INTO curr_user_id,curr_person_name,uum_sync_code,mis_key;
          EXIT WHEN c_person%NOTFOUND;

          /* 获取序列值 */
          SELECT NEXT_VAL INTO temp_person_id FROM T_COMM_SEQUENCE WHERE NAME='SEQ_ORGAN_PERSON';
          UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_person_id+1) WHERE NAME='SEQ_ORGAN_PERSON';

          /**插入人员表 **/
          INSERT INTO t_organ_person
          (
             ID,NAME,LAST_NAME,FIRST_NAME,SEX,
             BIRTHDAY,CREATOR,BEGIN,END
          )
          VALUES
          (
              temp_person_id,curr_person_name,
              substr(curr_person_name,0,1),
              substr(curr_person_name,2,length(curr_person_name)),
              '1',sysdate,'admin',
              sysdate,to_date('9999-12-30 23:59','yyyy-mm-dd hh24:mi')
          );

          /* 获取序列值 */
          SELECT NEXT_VAL INTO temp_user_id FROM T_COMM_SEQUENCE WHERE NAME='S_SECU_USER';
          UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_user_id+1) WHERE NAME='S_SECU_USER';
          /**插入用户表 **/
          INSERT INTO T_SECU_USER
          (
              ID,USER_ID,PERSON,SYSN_CODE,CREATOR,LAST_LOGIN_TIME,
              LAST_UPDATE_TIME,LAST_MODIFY_TIME,PASSWORD,IS_VALID,
              FORUM_PASSWORD,MIS_KEY,BEGIN,END
          )
          VALUES
          (
              temp_user_id,curr_user_id,temp_person_id,uum_sync_code,
              'admin',sysdate,sysdate,sysdate,
              '123456','1','123456',mis_key,
              sysdate,to_date('9999-12-30 23:59','yyyy-mm-dd hh24:mi')
          );
          /**插入用户默认角色表 **/
          /* 获取序列值 */
          SELECT NEXT_VAL INTO temp_roleuser_id FROM T_COMM_SEQUENCE WHERE NAME='S_SECU_ROLE_RELA_USER';
          UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_roleuser_id+1) WHERE NAME='S_SECU_ROLE_RELA_USER';
          /* 插入数据表 */
          INSERT INTO T_SECU_ROLE_RELA_USER
          (
              ID,SECU_USER,SECU_ROLE,CREATOR,BEGIN,END
          )
          VALUES
          (
              temp_roleuser_id,temp_user_id,
              (
                select S.ID from T_SECU_ROLE S
                where S.IS_DEFAULT=1
              )
              ,'admin',sysdate,to_date('9999-12-30 23:59','yyyy-mm-dd hh24:mi')
          );
          
     END LOOP;
     CLOSE c_person;
END ;




/*组织同步*/
CREATE OR REPLACE PROCEDURE PROD_SYSN_ORG
(
       varAreaCode in varchar2
)
IS
     v_sql varchar2(1000); /* sql */
     seq_sql  varchar2(1000); /* 序列sql */
     corp_sql  varchar2(1000); /* 公司sql */
     dept_sql  varchar2(1000); /* 部门sql */
     subdept_sql  varchar2(1000); /* 二级部门sql */
     person_sql  varchar2(1000); /* 人员sql */
     user_sql  varchar2(1000); /* 用户sql */
     temp_org_id number;  /* 组织id */
     temp_person_id varchar2(60);  /* 人员id */
     temp_user_id varchar2(60);  /* 用户id */
     temp_orgperson_id varchar2(60);  /* 组织人员id */
     temp number;  /* 临时保存数据 */
     currAreacode varchar2(10);  /* 当前地区代码 */
     currDept varchar2(32);  /* 当前部门代码 */
     currSubDept varchar2(32);  /* 当前子部门代码 */
     currPerson varchar2(32);  /* 当前人员代码 */

     dept_name varchar2(600);
     sub_dept_name varchar2(300);
     area_code varchar2(300);
     sup_org varchar2(32);
     code_id varchar2(300);

     /* 公司游标 */
     CURSOR c_corp IS
         SELECT
                a.area AS area,
                a.fullname AS fullname,
                TO_CHAR(a.areacode) AS areacode
         FROM prj_sareacode a
         WHERE a.areacode=varAreaCode;

     /* 一级部游标 */
     CURSOR c_dept(CORP_AREA VARCHAR2) IS
         SELECT
                a.dept AS name,
                a.areacode,
                a.codeid
         FROM
         (
                SELECT DISTINCT
                       TO_CHAR(t.areacode) AS areacode,t.dept,t.deptid as codeid
                FROM prj_sdept t
                where
                     TO_CHAR(t.areacode)=CORP_AREA
                     AND t.subdept IS NULL
          ) a;

     /* 二级部游标 */
     CURSOR c_subdept(CORP_AREA VARCHAR2,C_DEPT VARCHAR2) IS
          SELECT
               distinct t.areacode AS area_code,t.subdept as dept_name,
               t.deptid as code_id
          FROM prj_sdept t
          WHERE
               t.areacode=CORP_AREA
               AND  t.subdept IS NOT NULL
               AND t.dept=C_DEPT;

     /** 一级部用户 **/
     CURSOR c_dept_user(CORP_AREA VARCHAR2,DEPT_NAME VARCHAR2) IS
          SELECT
              distinct t.userid
          FROM  prj_suserdept t
          WHERE
               t.areacode=CORP_AREA
               AND  t.subdept IS NULL
               AND t.dept=DEPT_NAME
               AND t.userid IS NOT NULL;

     /** 二级部用户 **/
     CURSOR c_subdept_user(CORP_AREA VARCHAR2,USER_DEPT_NAME VARCHAR2,SUB_DEPT VARCHAR2) IS
          SELECT
              distinct t.userid
          FROM  prj_suserdept t
          WHERE
               t.areacode=CORP_AREA
               AND t.subdept IS NOT NULL
               AND t.dept=USER_DEPT_NAME
               AND t.subdept=SUB_DEPT
               AND t.userid IS NOT NULL;


     bad_credit EXCEPTION;

BEGIN

 --发生异常，控制转向；
 --RAISE bad_credit;

 /* 先导入公司，根据公司areacode找到公司的一级部门数据，再根据一级部门数据获取二级部门的数据 */

 FOR corp IN c_corp LOOP

     /* 判断记录是否已经存在 */
     SELECT count(ID) INTO temp FROM t_organ_organization
     WHERE CODE_ID=corp.areacode;

     currAreacode:=corp.areacode;

     IF (temp=0) THEN
         BEGIN

         /* 获取序列值 */
         SELECT NEXT_VAL INTO temp_org_id FROM T_COMM_SEQUENCE WHERE NAME='SEQ_ORGAN_ORGANIZATION';
         UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_org_id+1) WHERE NAME='SEQ_ORGAN_ORGANIZATION';

          /* 插入公司 */
         INSERT INTO
                t_organ_organization
                (
                   ID,NAME,ORG_TYPE,ORG_LEVEL,
                   DESCRIPTION,ORG_INDEX,AUTHOR,
                   CODE_ID,CREATOR,SUPERIOR,
                   BEGIN,END )
         VALUES
         (
                temp_org_id,corp.area,'CORP','1',
                corp.fullname,'1','1-'||temp_org_id,corp.areacode,'admin','0'
                ,SYSDATE,to_date('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
          );

         currAreacode:=temp_org_id;

         END;
     ELSE
         SELECT ID INTO currAreacode FROM t_organ_organization
         WHERE CODE_ID=corp.areacode;
     END IF;

     /* 插入一级部 */

     DBMS_OUTPUT.put_line('当前地区id：'||currAreacode);

     OPEN c_dept(corp.areacode);
     LOOP

          FETCH c_dept INTO dept_name,area_code,code_id;
          EXIT WHEN c_dept%NOTFOUND;

          /* 判断记录是否已经存在 */
          SELECT count(ID) INTO temp FROM t_organ_organization
          WHERE
                 SUPERIOR=currAreacode
                 AND CODE_ID=code_id
                 AND NAME=dept_name;

          /*DBMS_OUTPUT.put_line('存在记录：'||temp);*/

          IF (temp=0) THEN
              BEGIN
                  SELECT NEXT_VAL INTO temp_org_id FROM T_COMM_SEQUENCE WHERE NAME='SEQ_ORGAN_ORGANIZATION';
                  UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_org_id+1) WHERE NAME='SEQ_ORGAN_ORGANIZATION';
                  --DBMS_OUTPUT.put_line('一级部门名称：'||dept_name);

                  currDept:=temp_org_id;

                  INSERT INTO
                       t_organ_organization
                       (
                            ID,NAME,ORG_TYPE ,ORG_LEVEL,
                            DESCRIPTION ,ORG_INDEX,AUTHOR,
                            CODE_ID,SUPERIOR,
                            CREATOR,BEGIN,END
                        )
                   VALUES
                   (
                        temp_org_id,dept_name,'DEPT','2',
                        dept_name,'2','2-'||currAreacode||'-'||temp_org_id,
                        code_id,currAreacode,'admin',sysdate,
                        to_date('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
                   );

               END;
           ELSE
               SELECT ID INTO currDept FROM t_organ_organization
               WHERE
                   SUPERIOR=currAreacode
                   AND CODE_ID=code_id
                   AND NAME=dept_name;
           END IF;

           /**插入一级部人员**/
           OPEN c_dept_user(corp.areacode,dept_name);
           LOOP
                FETCH c_dept_user INTO temp_user_id;
                EXIT WHEN c_dept_user%NOTFOUND;

                /**查找对应人员**/
                SELECT count(person) INTO temp FROM t_secu_user
                WHERE user_id=temp_user_id;

                --DBMS_OUTPUT.put_line('一级人员名称：'||dept_name||temp_user_id||temp_person_id);
                IF (temp>0) THEN
                   SELECT person INTO temp_person_id FROM t_secu_user
                   WHERE user_id=temp_user_id;
                   /**查找关系表里是否有相关记录**/
                   SELECT count(ID) INTO temp FROM t_organ_org_rela_person
                   WHERE person=temp_person_id AND organization=currDept;

                   SELECT NEXT_VAL INTO temp_orgperson_id FROM T_COMM_SEQUENCE WHERE NAME='SEQ_ORGAN_ORG_RELA_PERSON';
                   UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_orgperson_id+1) WHERE NAME='SEQ_ORGAN_ORG_RELA_PERSON';

                   IF (temp=0) THEN
                      INSERT INTO t_organ_org_rela_person
                      (
                          id,person,organization,
                          creator,begin,end
                      )
                      VALUES
                      (
                          temp_orgperson_id,temp_person_id,currDept,
                          'admin',sysdate,to_date('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
                      );
                   END IF;
                END IF;
           END LOOP;
           CLOSE c_dept_user;


           /* 插入二级部 */
            SELECT
               count(t.areacode) INTO temp
            FROM prj_sdept t
            WHERE
               TO_CHAR(t.areacode)=corp.areacode
               AND  t.subdept IS NOT NULL
               AND t.dept=dept_name;
            IF  (temp>0) THEN
               BEGIN
                   OPEN c_subdept(corp.areacode,dept_name);
                   LOOP
                       FETCH c_subdept INTO area_code,sub_dept_name,code_id;
                       EXIT WHEN c_subdept%NOTFOUND;

                       --DBMS_OUTPUT.put_line('二级部门名称：'||area_code||sub_dept_name||code_id);
                       /*判断记录是否已经存在 */
                       SELECT count(ID) INTO temp FROM t_organ_organization
                       WHERE
                           SUPERIOR=currDept
                           AND CODE_ID=code_id
                           AND NAME=sub_dept_name;

                       IF (temp=0) THEN
                          BEGIN

                              SELECT NEXT_VAL INTO temp_org_id FROM T_COMM_SEQUENCE WHERE NAME='SEQ_ORGAN_ORGANIZATION';
                              UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_org_id+1) WHERE NAME='SEQ_ORGAN_ORGANIZATION';

                              --DBMS_OUTPUT.put_line('二级部门名称：'||sub_dept_name);

                              currSubDept:=temp_org_id;

                              INSERT INTO t_organ_organization
                              (
                                    ID,NAME,ORG_TYPE ,ORG_LEVEL,
                                    DESCRIPTION ,ORG_INDEX,AUTHOR,
                                    CODE_ID,SUPERIOR,
                                    CREATOR,BEGIN,END
                               )
                              VALUES
                              (
                                    temp_org_id,sub_dept_name,'DEPT','3',
                                    sub_dept_name,'2','2-'||currAreacode||'-'||currDept||'-'||temp_org_id,
                                    code_id,currDept,'admin',sysdate,
                                    to_date('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
                               );

                          END;
                       ELSE
                           SELECT ID INTO currSubDept FROM t_organ_organization
                           WHERE
                               SUPERIOR=currDept
                               AND CODE_ID=code_id
                               AND NAME=sub_dept_name;
                       END IF;

                       /**插入二级部人员**/
                       OPEN c_subdept_user(corp.areacode,dept_name,sub_dept_name);
                       LOOP
                            FETCH c_subdept_user INTO temp_user_id;
                            EXIT WHEN c_subdept_user%NOTFOUND;

                            /**查找对应人员**/
                            SELECT count(person) INTO temp FROM t_secu_user
                            WHERE user_id=temp_user_id;

                            --DBMS_OUTPUT.put_line('二级人员名称：'||dept_name||temp_user_id||temp_person_id);
                            IF (temp>0) THEN
                               SELECT person INTO temp_person_id FROM t_secu_user
                               WHERE user_id=temp_user_id;

                               /**查找关系表里是否有相关记录**/
                               SELECT count(ID) INTO temp FROM t_organ_org_rela_person
                               WHERE person=temp_person_id AND organization=currSubDept;

                               SELECT NEXT_VAL INTO temp_orgperson_id FROM T_COMM_SEQUENCE WHERE NAME='SEQ_ORGAN_ORG_RELA_PERSON';
                               UPDATE T_COMM_SEQUENCE SET  NEXT_VAL=(temp_orgperson_id+1) WHERE NAME='SEQ_ORGAN_ORG_RELA_PERSON';

                               IF (temp=0) THEN
                                  INSERT INTO t_organ_org_rela_person
                                  (
                                      id,person,organization,
                                      creator,begin,end
                                  )
                                  VALUES
                                  (
                                      temp_orgperson_id,temp_person_id,currSubDept,
                                      'admin',sysdate,to_date('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
                                  );
                               END IF;
                            END IF;
                       END LOOP;
                       CLOSE c_subdept_user;

                   END LOOP;

                   CLOSE c_subdept;

              END;
         END IF;

     END LOOP;

     CLOSE c_dept;

  END LOOP;

  --EXCEPTION
  --WHEN bad_credit THEN
       --DBMS_OUTPUT.put_line('出现异常');

END ;


